package com.hundsun.aitest.util.oracle;

import java.sql.*;
import java.util.Properties;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class OracleConnectUtils {

    // 数据库连接信息
    private static final String URL = "jdbc:oracle:thin:@//10.20.158.150:1521/pdb2";
    private static final String USER = "urp3";
    private static final String PASSWORD = "hundsun";
    private static final int CONNECT_TIMEOUT = 10000; // 连接超时时间，单位为毫秒

    // 获取数据库连接的方法
    private static Connection getConnection() throws Exception {
        Properties props = new Properties();
        props.setProperty("user", USER);
        props.setProperty("password", PASSWORD);
        props.setProperty("oracle.net.CONNECT_TIMEOUT", String.valueOf(CONNECT_TIMEOUT));

        // 加载Oracle驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 获取数据库连接
        return DriverManager.getConnection(URL, props);
    }

    // 安全关闭资源的方法
    private static void closeQuietly(AutoCloseable... closeables) {
        for (AutoCloseable closeable : closeables) {
            if (closeable != null) {
                try {
                    closeable.close();
                } catch (Exception ignored) {
                }
            }
        }
    }

    // 执行查询操作的方法
    public static JSONArray select(String sql) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        JSONArray array = new JSONArray();

        try {
            // 获取数据库连接
            conn = getConnection();
            // 创建Statement对象
            st = conn.createStatement();
            // 执行SQL查询
            rs = st.executeQuery(sql);

            // 处理结果集
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                JSONObject jsonObj = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = rs.getString(columnName);
                    jsonObj.put(columnName, value);
                }
                array.add(jsonObj);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭所有资源
            closeQuietly(rs, st, conn);
        }
        return array;
    }

    // 获取表的元数据的方法
    public static String metaData(String tableName) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        JSONArray array = new JSONArray();

        try {
            // 获取数据库连接
            conn = getConnection();
            // 创建Statement对象
            st = conn.createStatement();
            // 构建SQL查询语句
            String sql = String.format("SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) AS TABLE_DDL FROM USER_TABLES WHERE TABLE_NAME = '%s'", tableName);
            // 执行SQL查询
            rs = st.executeQuery(sql);

            // 处理结果集
            while (rs.next()) {
                JSONObject jsonObj = new JSONObject();
                String value = rs.getString("TABLE_DDL");
                jsonObj.put("TABLE_DDL", value);
                array.add(jsonObj);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭所有资源
            closeQuietly(rs, st, conn);
        }
        return array.getJSONObject(0).getString("TABLE_DDL");
    }

    // 获取列注释的方法
    public static JSONArray columnComments(String tableName) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        JSONArray array = new JSONArray();

        try {
            // 获取数据库连接
            conn = getConnection();
            // 创建Statement对象
            st = conn.createStatement();
            // 构建SQL查询语句
            String sql = String.format("SELECT COLUMN_NAME, COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME='%s'", tableName);
            // 执行SQL查询
            rs = st.executeQuery(sql);

            // 处理结果集
            while (rs.next()) {
                JSONObject jsonObj = new JSONObject();
                String key = rs.getString("COLUMN_NAME");
                String value = rs.getString("COMMENTS") == null ? "无" : rs.getString("COMMENTS");
                jsonObj.put(key, value);
                array.add(jsonObj);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭所有资源
            closeQuietly(rs, st, conn);
        }
        return array;
    }

    // 执行SQL更新操作的方法
    public static boolean runSQL(String sql) {
        Connection conn = null;
        Statement st = null;
        boolean success = false;

        try {
            // 获取数据库连接
            conn = getConnection();
            // 创建Statement对象
            st = conn.createStatement();
            // 执行SQL更新
            int result = st.executeUpdate(sql);
            success = result > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭所有资源
            closeQuietly(st, conn);
        }
        return success;
    }
}